﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WindowsFormsApplication1.Model;
using System.Data.SqlClient;
using System.Data;

namespace WindowsFormsApplication1.DAL
{
    class PersonDAL
    {
        public int addNew(Person model)
        {
            object obj = sqlHelper.ExecuteScalar(
                "insert into Person(Name,Age) output inserted.id values(@name,@age)",
                new SqlParameter("name", model.Name), new SqlParameter("age", model.Age));
            return Convert.ToInt32(obj);
        }

        public int delete(int id)
        {
            return sqlHelper.ExecuteNonQuery("delete from person where id = @id",
                new SqlParameter("id", id));
        }

        public int update(Person model)
        {
            return sqlHelper.ExecuteNonQuery("update person set name = @name,age = @age where id = @id",
                new SqlParameter("name", model.Name),
                new SqlParameter("age", model.Age),
                new SqlParameter("id", model.Id));
        }

        public Person GetPerson(int id)
        {
            DataTable dt = sqlHelper.ExecuteDataTable("select * from person where id = @id",
                new SqlParameter("id", id));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count == 1)
            {
                Person p = new Person();
                DataRow row = dt.Rows[0];
                p.Name = (string)row["name"];
                p.Id = (int)row["id"];
                p.Age = (int)row["age"];
                return p;
            }
            else
            {
                throw new Exception("出现多条数据");
            }
        }

        public IEnumerable<Person> GetAll()
        {
            DataTable dt = sqlHelper.ExecuteDataTable("select * from person");
            List<Person> persons = new List<Person>();
            foreach(DataRow row in dt.Rows)
            {
                Person model = new Person();
                model.Id = (int)row["id"];
                model.Name = (string)row["name"];
                model.Age = (int)row["age"];
                persons.Add(model);
            }
            return persons;
        }
    }
}
